package com.violet.sharding.config;

import com.violet.common.util.SpringUtil;
import com.violet.sharding.properties.EnvironmentProperty;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Objects;


/**
 * 自定义策略中调用该类方法 实现数据库无表则创建表的功能-- 不推荐 还是数据库定时自动创建好些
 */
@Slf4j
public class ShardingAlgorithmTool {

    private static final HashSet<String> TABLE_NAME_CACHE = new HashSet<>();

    /**
     * 判断 分表获取的表名是否存在 不存在则自动建表
     *
     * @param logicTableName  逻辑表名(表头)
     * @param resultTableName 真实表名
     * @return 确认存在于数据库中的真实表名
     */
    public static String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {
        synchronized (logicTableName.intern()) {
            // 缓存中有此表 返回
            if (TABLE_NAME_CACHE.contains(resultTableName)) {
                return resultTableName;
            }
            // 缓存中无此表 建表 并添加缓存
            List<String> sqlList = selectTableCreateSql(logicTableName);
            for (int i = 0; i < sqlList.size(); i++) {
                sqlList.set(i, sqlList.get(i).replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS").replace(logicTableName, resultTableName));
            }
            executeSql(sqlList);
            TABLE_NAME_CACHE.add(resultTableName);
        }
        return resultTableName;
    }

    /**
     * 缓存重载方法
     */
    public static void tableNameCacheReload() {
        // 读取数据库中所有表名
        List<String> tableNameList = getAllTableNameBySchema();
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.TABLE_NAME_CACHE.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.TABLE_NAME_CACHE.addAll(tableNameList);
    }


    private static void executeSql(List<String> sqlList) {
        EnvironmentProperty environmentProperty = SpringUtil.getBean(EnvironmentProperty.class);
        try (Connection conn = DriverManager.getConnection(Objects.requireNonNull(environmentProperty.getUrl()),
                environmentProperty.getUsername(),
                environmentProperty.getPassword())) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
                conn.commit();
            } catch (Exception ex) {
                conn.rollback();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    private static List<String> selectTableCreateSql(String tableName) {
        List<String> res = new ArrayList<>();
        if ("eca_alarm".equals(tableName)) {
            res.add("CREATE TABLE `eca_alarm` (\n" +
                    "  `id` bigint unsigned NOT NULL,\n" +
                    "  `type_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '设备类型id',\n" +
                    "  `equipment_id` bigint unsigned NOT NULL COMMENT '设备id',\n" +
                    "  `data` json DEFAULT NULL COMMENT '原始数据',\n" +
                    "  `parse_data` json DEFAULT NULL COMMENT '解析数据',\n" +
                    "  `parse_time` bigint NOT NULL DEFAULT '0' COMMENT '解析时间',\n" +
                    "  `create_time` bigint NOT NULL DEFAULT '0',\n" +
                    "  PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='设备历史数据'");
            res.add("ALTER TABLE `hss_history` ADD INDEX n1 ( `create_time`, `equipment_id` ) USING BTREE");
            res.add("ALTER TABLE `hss_history` ADD INDEX n2 ( `equipment_id` ) USING BTREE");
            res.add("ALTER TABLE `hss_history` ADD INDEX n3 ( `parse_time` ) USING BTREE");
        }
        return res;
    }

    public static List<String> getAllTableNameBySchema() {
        EnvironmentProperty environmentProperty = SpringUtil.getBean(EnvironmentProperty.class);
        List<String> res = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(environmentProperty.getUrl(),
                environmentProperty.getUsername(),
                environmentProperty.getPassword());
             Statement st = connection.createStatement()) {
            try (ResultSet rs = st.executeQuery("show TABLES like 'eca_alarm%'")) {
                while (rs.next()) {
                    res.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public static HashSet<String> cacheTableNames() {
        return TABLE_NAME_CACHE;
    }
}